Imports System.Data.SqlClient
Imports System.Text

Public Class MSSqlConnHelper
    Implements Katze.IConnHelper

    Private _ConnStr As String
    Public Property ConnStr() As String Implements IConnHelper.ConnStr
        Get
            Return _ConnStr
        End Get
        Set(ByVal value As String)
            _ConnStr = value
        End Set
    End Property

    Public Sub New(ByVal ConnStr As String)
        Me.ConnStr = ConnStr
    End Sub

    Public Function exeDataRow(ByVal sql As String) As System.Data.DataRow Implements IConnHelper.exeDataRow
        Return exeDataRow(sql, CommandType.Text, Nothing)
    End Function

    Public Function exeDataRow(ByVal sql As String, ByVal param As IParaHelper) As System.Data.DataRow Implements IConnHelper.exeDataRow
        Return exeDataRow(sql, CommandType.Text, param)
    End Function

    Public Function exeDataRow(ByVal sql As String, ByVal sp As System.Data.CommandType, ByVal param As IParaHelper) As System.Data.DataRow Implements IConnHelper.exeDataRow
        Dim dt As DataTable
        dt = exeTable(sql, sp, param)
        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)
        Else
            Throw New HelperExceptions.NullDataExcetion
        End If
    End Function

    Public Function exeInt(ByVal sql As String) As Integer Implements IConnHelper.exeInt
        Return exeInt(sql, CommandType.Text, Nothing)
    End Function

    Public Function exeInt(ByVal sql As String, ByVal param As IParaHelper) As Integer Implements IConnHelper.exeInt
        Return exeInt(sql, CommandType.Text, param)
    End Function

    Public Function exeInt(ByVal sql As String, ByVal sp As System.Data.CommandType, ByVal param As IParaHelper) As Integer Implements IConnHelper.exeInt
        Dim o As Object
        o = exeScalar(sql, sp, param)
        If o Is Nothing Then
            Throw New HelperExceptions.NullDataExcetion
        Else
            Return CInt(o)
        End If
    End Function

    Public Function exeGetSchema() As DataTable
        Dim cn As New SqlConnection(Me.ConnStr)

        cn.Open()
        Dim cmd As New SqlCommand("", cn)

        Dim dt As DataTable
        dt = cn.GetSchema("Columns")
        cn.Close()
        Return dt
    End Function

    Public Function exeNonQuery(ByVal sql As String) As Integer Implements IConnHelper.exeNonQuery
        Return exeNonQuery(sql, CommandType.Text, Nothing)
    End Function

    Public Function exeNonQuery(ByVal sql As String, ByVal param As IParaHelper) As Integer Implements IConnHelper.exeNonQuery
        Return exeNonQuery(sql, CommandType.Text, param)
    End Function

    Public Function exeNonQuery(ByVal sql As String, ByVal sp As System.Data.CommandType, ByVal param As IParaHelper) As Integer Implements IConnHelper.exeNonQuery
        Dim cn As New SqlConnection(Me.ConnStr)
        Dim i As Integer
        cn.Open()
        Dim cmd As New SqlCommand(sql, cn)
        cmd.CommandType = sp

        If Not param Is Nothing Then
            For Each p As SqlParameter In param.Parameters
                cmd.Parameters.Add(p)
            Next
        End If
        Try
            Return cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            cmd.Dispose()
            cn.Close()
            cn.Dispose()
        End Try
    End Function

    Public Function exeScalar(ByVal sql As String) As Object Implements IConnHelper.exeScalar
        Return exeScalar(sql, CommandType.Text, Nothing)
    End Function

    Public Function exeScalar(ByVal sql As String, ByVal param As IParaHelper) As Object Implements IConnHelper.exeScalar
        Return exeScalar(sql, CommandType.Text, param)
    End Function

    Public Function exeScalar(ByVal sql As String, ByVal sp As System.Data.CommandType, ByVal param As IParaHelper) As Object Implements IConnHelper.exeScalar
        Dim cn As New SqlConnection(Me.ConnStr)
        cn.Open()
        Dim cmd As New SqlCommand(sql, cn)
        cmd.CommandType = sp
        If Not param Is Nothing Then
            For Each p As SqlParameter In param.Parameters
                cmd.Parameters.Add(p)
            Next
        End If
        Dim o As Object
        Try
            o = cmd.ExecuteScalar()
            Return o
        Catch ex As Exception
            Throw ex
        Finally
            cmd.Dispose()
            cn.Close()
            cn.Dispose()
        End Try
    End Function

    Public Function exeStr(ByVal sql As String) As String Implements IConnHelper.exeStr
        Return exeStr(sql, CommandType.Text, Nothing)
    End Function

    Public Function exeStr(ByVal sql As String, ByVal param As IParaHelper) As String Implements IConnHelper.exeStr
        Return exeStr(sql, CommandType.Text, param)
    End Function

    Public Function exeStr(ByVal sql As String, ByVal sp As System.Data.CommandType, ByVal param As IParaHelper) As String Implements IConnHelper.exeStr
        Dim o As Object
        o = exeScalar(sql, sp, param)
        If o Is Nothing Then
            Throw New HelperExceptions.NullDataExcetion
        Else
            Return CStr(o)
        End If
    End Function

    Public Function exeTable(ByVal sql As String) As System.Data.DataTable Implements IConnHelper.exeTable
        Return exeTable(sql, CommandType.Text, Nothing)
    End Function

    Public Function exeTable(ByVal sql As String, ByVal param As IParaHelper) As System.Data.DataTable Implements IConnHelper.exeTable
        Return exeTable(sql, CommandType.Text, param)
    End Function

    Public Function exeTable(ByVal sql As String, ByVal sp As System.Data.CommandType, ByVal param As IParaHelper) As System.Data.DataTable Implements IConnHelper.exeTable
        Dim cn As New SqlConnection(Me.ConnStr)
        cn.Open()
        Dim com As New SqlDataAdapter(sql, cn)
        com.SelectCommand.CommandType = sp
        If Not param Is Nothing Then
            For Each p As SqlParameter In param.Parameters
                com.SelectCommand.Parameters.Add(p)
            Next
        End If
        Dim ds As New DataTable
        Try
            com.Fill(ds)
            Return ds
        Catch ex As Exception
            Throw ex
        Finally
            com.Dispose()
            cn.Close()
            cn.Dispose()
        End Try
    End Function

    Public Function GetPagingSQL(ByVal sql As String, ByVal OrderSQL As String, ByVal start As Integer, ByVal count As Integer) As String Implements IConnHelper.GetPagingSQL
        Dim i As Integer
        Dim r As String
        i = sql.IndexOf("from")

        r = "select * from (" & sql.Substring(0, i) & ",ROW_NUMBER() over (order by " & OrderSQL & ") as rowNum "
        r &= sql.Substring(i) & ") as mytable where rowNum between " & Str(start) & " and " & Str(start + count - 1)

        Return r
    End Function

    Function GetParaHelper() As IParaHelper Implements IConnHelper.GetParaHelper
        Return New MSSQLParaHelper
    End Function


End Class
